  
  Create       PROC sp_yielded_cost_info_report( 
  	@FG_PART_NUMBER VARCHAR(50), 
  	@LOTS_DATE_OUT_FROM VARCHAR(20), 
  	@LOTS_DATE_OUT_TO VARCHAR(20), 
  	@STEP_SIZE INT ) AS
  BEGIN
  	DECLARE @fg_process_step_id INT
  	DECLARE @grossDiePerWafer INT
  	DECLARE @date_out_start DATETIME
  	DECLARE @date_out_end DATETIME
  	DECLARE @date_out_start_iterator DATETIME
  	declare @quarter varchar(50)
  	declare @year varchar(4)
  
  	DECLARE @processStepId INT
  	DECLARE @processType VARCHAR(50)
  	DECLARE @baseProcessType VARCHAR(50)
  
  	DECLARE @fab_vendorName VARCHAR(50)
  	DECLARE @fab_cycleTime INT
  	DECLARE @fab_yield FLOAT
  	DECLARE @fab_cost FLOAT
  	DECLARE @total_fab_cycleTime INT
  	DECLARE @total_fab_yield FLOAT
  	DECLARE @total_fab_cost FLOAT
  	DECLARE @fab_location_id INT
  
  	DECLARE @sort_vendorName VARCHAR(50)
  	DECLARE @sort_cycleTime INT
  	DECLARE @sort_yield FLOAT
  	DECLARE @sort_cost FLOAT
  	DECLARE @total_sort_cycleTime INT
  	DECLARE @total_sort_yield FLOAT
  	DECLARE @total_sort_cost FLOAT
  	DECLARE @sort_location_id INT
  
  	DECLARE @assembly_vendorName VARCHAR(50)
  	DECLARE @assembly_cycleTime INT
  	DECLARE @assembly_yield FLOAT
  	DECLARE @assembly_cost FLOAT
  	DECLARE @total_assembly_cycleTime INT
  	DECLARE @total_assembly_yield FLOAT
  	DECLARE @total_assembly_cost FLOAT
  	DECLARE @assembly_location_id INT
  
  	DECLARE @test_vendorName VARCHAR(50)
  	DECLARE @test_cycleTime INT
  	DECLARE @test_yield FLOAT
  	DECLARE @test_cost FLOAT
  	DECLARE @total_test_cycleTime INT
  	DECLARE @total_test_yield FLOAT
  	DECLARE @total_test_cost FLOAT
  	DECLARE @test_location_id INT
  
  	DECLARE @fg_vendorName VARCHAR(50)
  
  	DECLARE @RESULTS TABLE( FG_PART_NUMBER VARCHAR(50), GROSS_DIE_WAFER INT, DATE_START DATETIME, DATE_END DATETIME,
  				FAB_VENDOR VARCHAR(50), FAB_CYCLE_TIME INT, FAB_YIELD NUMERIC( 9, 2), FAB_COST NUMERIC( 9, 2),
  				SORT_VENDOR VARCHAR(50), SORT_CYCLE_TIME INT, SORT_YIELD NUMERIC( 9, 2), SORT_COST NUMERIC( 9, 2),
  				ASSEMBLY_VENDOR VARCHAR(50), ASSEMBLY_CYCLE_TIME INT, ASSEMBLY_YIELD NUMERIC( 9, 2), ASSEMBLY_COST NUMERIC( 9, 2),
  				TEST_VENDOR VARCHAR(50), TEST_CYCLE_TIME INT, TEST_YIELD NUMERIC( 9, 2), TEST_COST NUMERIC( 9, 2),
  				FG_VENDOR VARCHAR(50) )
  
  	DECLARE @process_step_cursor CURSOR
  
  	IF @LOTS_DATE_OUT_FROM IS NULL OR @LOTS_DATE_OUT_FROM = '' OR @LOTS_DATE_OUT_FROM = '0'
  	BEGIN
  		SELECT @LOTS_DATE_OUT_FROM = '01/01/1900'
  	END
  
  	IF @LOTS_DATE_OUT_TO IS NULL OR @LOTS_DATE_OUT_TO = '' OR @LOTS_DATE_OUT_TO = '0'
  	BEGIN
  		SELECT @LOTS_DATE_OUT_TO = '01/01/9999'
  	END
  
  	SELECT @date_out_start = CC_Start_Date FROM COMPANY_Calendar WHERE CC_YEAR = ( SELECT MIN( DATEPART ( yyyy , Fin_Year ) ) FROM UPDATE_VIEW_COST_TABLE  )
 	
 	IF @date_out_start < CAST( @LOTS_DATE_OUT_FROM AS datetime)
 	BEGIN
 		SET @date_out_start =CAST( @LOTS_DATE_OUT_FROM AS datetime)
 	END
 	
  	SELECT @date_out_end = CC_End_Date FROM COMPANY_Calendar WHERE CC_YEAR = ( SELECT MAX( DATEPART ( yyyy , Fin_Year ) ) FROM UPDATE_VIEW_COST_TABLE  )
 
 	IF @date_out_end > CAST( @LOTS_DATE_OUT_TO AS datetime)
 	BEGIN
 		SET @date_out_end =  CAST( @LOTS_DATE_OUT_TO AS datetime)
 	END

  	SELECT @fg_process_step_id = PROCESSSTEP_ID FROM PRODUCT WHERE P_MARKETING_PART_NUMBER = @FG_PART_NUMBER 
  	SELECT TOP 1 @fg_vendorName = L_Vendor_Name FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @fg_process_step_id ORDER BY PSLT_PRIORITY
  	
  	SELECT @date_out_start_iterator = @date_out_start
  
  	WHILE @date_out_start_iterator <= @date_out_end
  	BEGIN
  		SET  @fab_vendorName = null
  		SET  @fab_cycleTime = 0
  		SET  @fab_yield = 1
  		SET  @fab_cost = 0.0
  		SET  @total_fab_cycleTime = 0
  		SET  @total_fab_yield = 1
  		SET  @total_fab_cost = 0.0
  		SET  @fab_location_id = 0
  		
  		SET  @sort_vendorName = null
  		SET  @sort_cycleTime = 0
  		SET  @sort_yield = 1
  		SET  @sort_cost = 0.0
  		SET  @total_sort_cycleTime = 0
  		SET  @total_sort_yield = 1
  		SET  @total_sort_cost = 0.0
  		SET  @sort_location_id = 0
  		
  		SET  @assembly_vendorName = null
  		SET  @assembly_cycleTime = 0
  		SET  @assembly_yield = 1
  		SET  @assembly_cost = 0.0
  		SET  @total_assembly_cycleTime = 0
  		SET  @total_assembly_yield = 1
  		SET  @total_assembly_cost = 0.00
  		SET  @assembly_location_id = 0
  		
  		SET  @test_vendorName = null
  		SET  @test_cycleTime = 0
  		SET  @test_yield = 1
  		SET  @test_cost = 0.0
  		SET  @total_test_cycleTime = 0 
  		SET  @total_test_yield = 1
  		SET  @total_test_cost = 0.0
  		SET  @test_location_id = 0
		
  		SET @process_step_cursor = CURSOR FOR SELECT ProcessStep_ID, PS_Type, Process_Type_Name FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID( @fg_process_step_id ) WHERE Process_Type_Name IN ( 'FAB', 'SORT', 'Assembly', 'TEST' ) 
  		select TOP 1 @quarter = LTRIM( RTRIM (CQC_Quarter )), @year = CC_Year from Company_calendar, Company_Quarterly_calendar where Company_calendar.CC_YearID = Company_Quarterly_calendar.CQC_YearID and CQC_START_Date <= @date_out_start_iterator order by CQC_START_Date DESC
  		print @year
  		OPEN @process_step_cursor
  		FETCH NEXT FROM @process_step_cursor INTO @processStepId, @processType, @baseProcessType
  		WHILE @@FETCH_STATUS = 0
  		BEGIN
  			IF @baseProcessType = 'FAB'
  			BEGIN
  				SELECT @grossDiePerWafer = Parameter_Value FROM Process_Parameter WHERE processStep_ID = @processStepId and Parameter_Key = 'Gross Die/Wafer'
  				SELECT TOP 1 @fab_cycleTime = PSLT_Cycle_Time_Normal, @fab_vendorName = L_Vendor_Name, @fab_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
  				
  				IF @quarter = 'QUARTER 1' or @quarter = 'Quarter1'
  				BEGIN
  					select @fab_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'QUARTER 2' or @quarter = 'Quarter2'
  				BEGIN
  					select @fab_cost = Q2_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'QUARTER 3' or @quarter = 'Quarter3'
  				BEGIN
  					select @fab_cost = Q3_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'QUARTER 4' or @quarter = 'Quarter4'
  				BEGIN
  					select @fab_cost = Q4_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				
  				IF @fab_cycleTime IS NOT NULL
  				BEGIN
  					SET @total_fab_cycleTime = @total_fab_cycleTime + @fab_cycleTime
  				END
  
  				IF @fab_cost IS NOT NULL
  				BEGIN
  					SET @total_fab_cost = @total_fab_cost + @fab_cost
  				END
  				
				declare fabLotCursor cursor for
				SELECT yield FROM fn_yield_all() WHERE ProcessStepID = @processStepId and ps_type_name = 'Fab' 
                                open fabLotCursor
				fetch next from fabLotCursor into @fab_yield
				while @@fetch_status = 0
  				begin
	  				IF @fab_yield IS NOT NULL
	  				BEGIN
	  					SET @total_fab_yield = ( @total_fab_yield * @fab_yield ) / 100
	  				END
				fetch next from fabLotCursor into @fab_yield
				end
  				close fabLotCursor
  				deallocate fabLotCursor
  			END
  			ELSE IF @baseProcessType = 'SORT'
  			BEGIN	
  				SELECT TOP 1 @sort_cycleTime = PSLT_Cycle_Time_Normal, @sort_vendorName = L_Vendor_Name, @sort_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
  			
  				IF @quarter = 'Quarter 1' or @quarter = 'Quarter1'
  				BEGIN	
  					select @sort_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'Quarter 2' or @quarter = 'Quarter2'
  				BEGIN
  					select @sort_cost = Q2_Standard_Cost  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'Quarter 3' or @quarter = 'Quarter3'
  				BEGIN
  					select @sort_cost = Q3_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
  				BEGIN
  					select @sort_cost = Q4_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  
  				IF @sort_cycleTime IS NOT NULL
  				BEGIN
  					SET @total_sort_cycleTime = @total_sort_cycleTime + @sort_cycleTime
  				END
  
  				IF @sort_cost IS NOT NULL
  				BEGIN
  					SET @total_sort_cost = @total_sort_cost + @sort_cost
  				END

				declare sortLotCursor cursor for
				SELECT yield FROM fn_yield_all() WHERE ProcessStepID = @processStepId and ps_type_name = 'SORT'					
				open sortLotCursor
				fetch next from sortLotCursor into @sort_yield
				while @@fetch_status = 0
  				begin	
  
	  				IF @sort_yield IS NOT NULL
	  				BEGIN
	  					SET @total_sort_yield = (@total_sort_yield * @sort_yield ) / 100
	  				END
				fetch next from sortLotCursor into @sort_yield
				end
  				close sortLotCursor
  				deallocate sortLotCursor
				
  			END
  			ELSE IF @baseProcessType = 'Assembly'
  			BEGIN
  				SELECT TOP 1 @assembly_cycleTime = PSLT_Cycle_Time_Normal, @assembly_vendorName = L_Vendor_Name, @assembly_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID  FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
  				IF @quarter = 'Quarter 1' or @quarter = 'Quarter1'
  				BEGIN	
  					select @assembly_cost = Q1_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'Quarter 2' or @quarter = 'Quarter2'
  				BEGIN
  					select @assembly_cost = Q2_Standard_Cost from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'Quarter 3' or @quarter = 'Quarter3'
  				BEGIN
  					select @assembly_cost = Q3_Standard_Cost  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
  				BEGIN
  					select @assembly_cost = Q4_Standard_Cost  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  
  				IF @assembly_cycleTime IS NOT NULL
  				BEGIN
  					SET @total_assembly_cycleTime = @total_assembly_cycleTime + @assembly_cycleTime
  				END
  
  				IF @assembly_cost IS NOT NULL
  				BEGIN
  					SET @total_assembly_cost = @total_assembly_cost + @assembly_cost
  				END
  				
				declare  assemblyLotCursor  cursor for
				SELECT yield FROM fn_yield_all() WHERE ProcessStepID = @processStepId and ps_type_name = 'Assembly'				
				open assemblyLotCursor
				fetch next from assemblyLotCursor into @assembly_yield
				while @@fetch_status = 0
  				begin	
	  				IF @assembly_yield IS NOT NULL
	  				BEGIN
	 
	  					SET @total_assembly_yield = (@total_assembly_yield * @assembly_yield ) / 100
	  				END
				fetch next from assemblyLotCursor into @assembly_yield
				end
				close assemblyLotCursor
				deallocate assemblyLotCursor
				
  			END
  			ELSE IF @baseProcessType = 'TEST'
  			BEGIN
  				SELECT TOP 1 @test_cycleTime = PSLT_Cycle_Time_Normal, @test_vendorName = L_Vendor_Name, @test_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID  FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
  				IF @quarter = 'Quarter 1' or @quarter = 'Quarter1'
  				BEGIN	
  					select @test_cost = Q1_Standard_Cost  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'Quarter 2' or @quarter = 'Quarter2'
  				BEGIN
  					select @test_cost = Q2_Standard_Cost  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF @quarter = 'Quarter 3' or @quarter = 'Quarter3'
  				BEGIN
  					select @test_cost = Q3_Standard_Cost   from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  				ELSE IF  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
  				BEGIN
  					select @test_cost = Q4_Standard_Cost  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
  				END
  
  				IF @test_cycleTime IS NOT NULL
  				BEGIN
  					SET @total_test_cycleTime = @total_test_cycleTime + @test_cycleTime
  				END
  
  				IF @test_cost IS NOT NULL
  				BEGIN
  					SET @total_test_cost = @total_test_cost + @test_cost
  				END
				
				declare testLotCursor cursor  for	
				SELECT yield FROM fn_yield_all() WHERE ProcessStepID = @processStepId and ps_type_name = 'TEST'
				open testLotCursor
				fetch next from testLotCursor into @test_yield
  				while @@fetch_status = 0
  				begin
	  				IF @test_yield IS NOT NULL
	  				BEGIN
	  					SET @total_test_yield = (@total_test_yield * @test_yield)/100
	  				END
				fetch next from testLotCursor into @test_yield
				end
				close testLotCursor
				deallocate testLotCursor
  
  			END
  		
  			FETCH NEXT FROM @process_step_cursor INTO @processStepId, @processType, @baseProcessType
  		END
  		CLOSE @process_step_cursor
  	    	DEALLOCATE @process_step_cursor
  
  		INSERT INTO @RESULTS ( FG_PART_NUMBER, GROSS_DIE_WAFER, DATE_START, DATE_END,
  				FAB_VENDOR, FAB_CYCLE_TIME, FAB_YIELD, FAB_COST,
  				SORT_VENDOR, SORT_CYCLE_TIME, SORT_YIELD, SORT_COST,
  				ASSEMBLY_VENDOR, ASSEMBLY_CYCLE_TIME, ASSEMBLY_YIELD, ASSEMBLY_COST,
  				TEST_VENDOR, TEST_CYCLE_TIME, TEST_YIELD, TEST_COST, FG_VENDOR ) VALUES
  				( @FG_PART_NUMBER, @grossDiePerWafer, @date_out_start_iterator, ( @date_out_start_iterator + @STEP_SIZE ),
  				  @fab_vendorName, @total_fab_cycleTime, @total_fab_yield * 100, @total_fab_cost,
  				  @sort_vendorName, @total_sort_cycleTime, @total_sort_yield * 100, @total_sort_cost,
  				  @assembly_vendorName, @total_assembly_cycleTime, @total_assembly_yield * 100, @total_assembly_cost,
  				  @test_vendorName, @total_test_cycleTime, @total_test_yield*100, @total_test_cost, @fg_vendorName
  				 )
  
  
  		SELECT @date_out_start_iterator = @date_out_start_iterator + @STEP_SIZE
  	END	
  	SELECT * FROM @results 
  END


